__author__ = 'Administrator'
# coding=utf-8

"""
Mysql MariaDB数据库的操作工具类
"""
import pymysql
import configparser
import os
import string


class MySqlUtil(object):
    conn = None
    _db_config_file = ""
    _cursor_type = "list"
    cf = None

    def __init__(self, _db_config_file=None, _cursor_type="list"):
        self._db_config_file = _db_config_file
        self._cursor_type = _cursor_type

    def connect_from_config(self, _db_config_file=None, _cursor_type="list"):

        if _db_config_file is not None:
            self._db_config_file = _db_config_file

        if not os.path.isfile(self._db_config_file):
            raise "数据库定义文件不存在，请检查配置文件" + self._db_config_file

        self.cf = configparser.ConfigParser()
        self.cf.read(self._db_config_file)

        _host = self.cf.get("db", "host")
        _user = self.cf.get("db", "username")
        _password = self.cf.get("db", "password")
        _port = self.cf.getint("db", "port")
        _db = self.cf.get("db", "database")

        if _host is None or _user is None or _password is None or _port is None or _db is None:
            raise "数据库连接参数缺失，请检查配置文件" + self._db_config_file

        self.connect(_host, _user, _password, _db, _port)

    def connect(self, _host, _user, _password, _db, _port=3306, _charset='utf8mb4'):
        # cursorclass=pymysql.cursors.DictCursor
        try:
            if self._cursor_type == "list":
                self.conn = pymysql.connect(host=_host, user=_user, passwd=_password, db=_db, port=_port,
                                            charset=_charset)
            else:
                self.conn = pymysql.connect(host=_host, user=_user, passwd=_password, db=_db, port=_port,
                                            charset=_charset, cursorclass=pymysql.cursors.DictCursor)
            return self.conn
        except RuntimeError:
            raise "数据库连接失败:" + "host=" + _host + ",db=" + _db

    def getConn(self):
        return self.conn

    def commit(self):
        if self.conn is None:
            return
        else:
            self.conn.commit()

    def rollback(self):
        if self.conn is None:
            return
        else:
            self.conn.rollback()

    def close(self):
        if self.conn is None:
            return
        else:
            self.conn.close()
            self.conn = None

    def select(self, sql):
        try:
            if self.conn is None:
                self.getConn(self)
            cur = self.conn.cursor()  # 获取一个游标
            cur.execute(sql)
            data = cur.fetchall()
            cur.close()  # 关闭游标
            return data
            # self.conn.close()  # 释放数据库资源
        except Exception as e:
            raise RuntimeError("数据库查询失败", e.__str__())

    def print_all_data(self, data):
        if self._cursor_type == "list":
            print("列表类型输出")
            for d in data:
                print("ID: " + str(d[0]) + '  title： ' + d[1] + "  url： " + d[2])
        else:
            print("字典类型输出")
            for d in data:
                print("ID: " + str(d['id']) + " title:" + d['title'] + "url:" + d['url'])

    def insert(self, sql):
        try:
            if self.conn is None:
                self.getConn()
            cur = self.conn.cursor()  # 获取一个游标
            cur.execute(sql)  # 执行SQL语句
            # conn.commit()  # 提交

            cur.close()  # 关闭游标
            # conn.close()  # 释放数据库资源
        except Exception as e:
            print("发生异常")
            raise RuntimeError("数据库插入失败", e.__str__())


if __name__ == "__main__":
    mysql = MySqlUtil("E:\\workspace_p\\crawler3\\net\\yfsun\\crawler3\\fang\\config.ini")
    mysql.connect_from_config()
    mysql.insert("insert into fang (title,url) VALUE ('testTitle','url')")
    mysql.commit()
    data = mysql.select("select * from fang")
    mysql.print_all_data(data)
    mysql.close()
